﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using WebTemplate.DAL.Entities;

namespace WebTemplate.DAL.Repositories
{
    public class StudentRepostiry
    {
        public void insert(Student One)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "[Student_Insert]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@MemberID",One.MemberID);
            command.Parameters.AddWithValue("@Department" , One.Department);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public void Update(Student One)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "[Student_Update]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@ID" , One.ID);
            command.Parameters.AddWithValue("@MemberID", One.MemberID);
            command.Parameters.AddWithValue("@Department", One.Department);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public void Delete(int ID)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "[Student_Delete]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@ID", ID);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public List<Student> Select(StudentSearchCriteria One)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "[Student_Select]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@ID", One.ID);
            command.Parameters.AddWithValue("@MemberID", One.MemberID);
            command.Parameters.AddWithValue("@Department", One.Department);

            SqlDataReader reader = command.ExecuteReader();
            List<Student> reg = new List<Student>();

            while (reader.Read())
            {
                Student obj = new Student();

                obj.ID = reader.GetInt32(0);
                obj.MemberID = reader.GetInt32(1);
                obj.Department = reader.GetString(2);

                reg.Add(obj);
            }

            connection.Close();
            return reg;

        }
    }
}
